# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/topics/item-pipeline.html

from livescore.db_settings import MySQLSettings
from twisted.enterprise import adbapi

from datetime import datetime
import MySQLdb.cursors
import MySQLdb
import re
import time
from livescore.items import MatchItem, HighlightItem, SquadItem

class LivescorePipeline(object):
    def process_item(self, item, spider):
        return item

class ProcessMatchItem(object):
    def __init__(self):
        # hardcoded db settings
        self.dbpool = adbapi.ConnectionPool('MySQLdb',
                db=MySQLSettings.db_name,
                user=MySQLSettings.username,
                passwd=MySQLSettings.password,
                cursorclass=MySQLdb.cursors.DictCursor,
                charset='utf8',
                use_unicode=True
        )

    def process_item(self, item, spider):
        # run db query in thread pool
        if isinstance(item, MatchItem) is True:
            query = self.dbpool.runInteraction(self._conditional_insert, item)
        return item
    
    def _conditional_insert(self, tx, item):
        tx.execute("select ID_Match from Url_Matches where ID_Match = %s",(item['ID_Match']))
        result = tx.fetchone()
        if result:
            update_query = "update Url_Matches set date = '" + str(item['date']) + "', kickOff = '" + str(item['kickOff'])+ "' where ID_Match = " + str(item['ID_Match'])
            #print update_query
            tx.execute(update_query)
        else:
            # Insert to Url_Matches table
            query = """insert into Url_Matches (ID_Match, date, url, kickOff, crawledDate) values (%s, %s, %s, %s, %s)"""
            params = (item['ID_Match'], item['date'], item['url'], item['kickOff'], item['crawledDate'])
            tx.execute(query, params)

        # find ID_Home from Teams in db, create one if  not existed
        tx.execute("select ID_Team from Teams use index(idx_teams) where nameTeam = %s", (item['home']))
        result = tx.fetchone()

        if result: # obtain ID_Home
            item['ID_Home'] = result['ID_Team']
        else: # insert new Team
            tx.execute("insert into Teams (nameTeam, logoUrl) values (%s, %s)",(item['home'], item['home_logoUrl']))
            item['ID_Home'] = tx.lastrowid
        
        # find ID_Away from Teams in db, create one if  not existed
        tx.execute("select ID_Team from Teams use index(idx_teams) where nameTeam = %s",(item['away']))
        result = tx.fetchone()

        if result: # obtain ID_Home
            item['ID_Away'] = result['ID_Team']
        else: # insert new Team
            tx.execute("insert into Teams (nameTeam, logoUrl) values (%s, %s)",(item['away'], item['away_logoUrl']))
            item['ID_Away'] = tx.lastrowid

        # find ID_League in db. create one if not existed
        tx.execute("select ID_League from Leagues where nameLeague = %s", (item['league'],))
        result = tx.fetchone()
        if result: # obtain ID_Leauge
            item['ID_League'] = result['ID_League']
        else: # insert new League
            tx.execute("insert into Leagues (nameLeague) values (%s)", (item['league']))
            item['ID_League'] = tx.lastrowid
        # find ID_Match in db. Create one if not existed
        tx.execute("select ID_Match from Matches where ID_Match = %s",(item['ID_Match']))
        result = tx.fetchone()
        if result:
            tx.execute("select gameMinute from Matches where ID_Match = %s",(item['ID_Match']))
            result = tx.fetchone()
            if result:
                for row in result:
                    if row[0] != -1 and item['gameMinute'] == 0:
                        item['gameMinute'] = -2
            update_query = "update Matches set homeScore = " + str(item['homeScore']) + ", awayScore = " + str(item['awayScore']) + " ,penHomeScore = " + str(item['penHomeScore']) + ", penAwayScore = " + str(item['penAwayScore']) + ", gameMinute = " + str(item['gameMinute']) + ", kickOff = '" + str(item['kickOff'])+ "' where ID_Match = " + str(item['ID_Match'])
            #print update_query
            tx.execute(update_query)
        else:
            if item['stadium'] != '':
                # Insert to Matches table
                query = """insert into Matches (ID_Match, date, homeScore,awayScore , penHomeScore,penAwayScore,stadium, ID_Home, ID_Away, ID_League, gameWeek, kickOff, gameMinute) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
                params =  ( item['ID_Match'] , item['date'], item['homeScore'], item['awayScore'], item['penHomeScore'], item['penAwayScore'], item['stadium'], item['ID_Home'], item['ID_Away'],item['ID_League'], item['gameWeek'], item['kickOff'], item['gameMinute'])
                #print query + params
                tx.execute(query, params)
        # -1 : haft-time
        # -2 : full-time
        # 0 : have not kick-off

class ProcessHighlightItem(object):
    def __init__(self):
        # hardcoded db settings
        self.dbpool = adbapi.ConnectionPool('MySQLdb',
                db=MySQLSettings.db_name,
                user=MySQLSettings.username,
                passwd=MySQLSettings.password,
                cursorclass=MySQLdb.cursors.DictCursor,
                charset='utf8',
                use_unicode=True
        )

    def process_item(self, item, spider):
        # run db query in thread pool
        if isinstance(item, HighlightItem) is True:
            query = self.dbpool.runInteraction(self._conditional_insert, item)
        return item
    
    def _conditional_insert(self, tx, item):
        tx.execute("select ID_Kind from Kind_Highlights use index(idx_kinds) where kind = %s", (item['kind']))
        result = tx.fetchone() 
        if result:
            item['ID_Kind'] = result['ID_Kind']
        else:
            query = """insert into Kind_Highlights (kind, pngUrl) values (%s, %s)"""
            params = (item['kind'], item['pngUrl'])
            tx.execute(query, params)
            item['ID_Kind'] = tx.lastrowid
        # find ID_Player from Players in db, create one if  not existed
        tx.execute("select ID_Player, ID_Team from Players use index(idx_players) where alias = %s", (item['player']))
        result = tx.fetchone()    
        if result:
            item['ID_Player'] = result['ID_Player']
            item['ID_Team'] = result['ID_Team']
        else:
            # find ID_Home from Teams in db, create one if  not existed
            tx.execute("select ID_Team from Teams  use index(idx_teams) where nameTeam = %s", (item['team']))
            result = tx.fetchone()
            while not result:
                # obtain ID_Team
                time.sleep(2)
                tx.execute("select ID_Team from Teams  use index(idx_teams) where nameTeam = %s", (item['team']))
                result = tx.fetchone()
            item['ID_Team'] = result['ID_Team']

            query = """insert into Players (alias, ID_Team) values (%s, %s)"""
            params = (item['player'], item['ID_Team'])
            tx.execute(query, params)
            item['ID_Player'] = tx.lastrowid
        # find ID_Player from Players in db, create one if  not existed
        if item['kind'] == 'SI':
            tx.execute("select ID_Player from Players use index(idx_players) where alias = %s", (item['sub_player']))
            result = tx.fetchone()    
            if result:
                item['ID_Sub_Player'] = result['ID_Player']
            else:
                query = """insert into Players (alias, ID_Team) values (%s, %s)"""
                params = (item['sub_player'], item['ID_Team'])
                tx.execute(query, params)
                item['ID_Sub_Player'] = tx.lastrowid
        else:
            item['ID_Sub_Player'] = 0

        # Check highlight is existed or not
        query = "select ID_Highlight from Highlights use index(idx_gameMinutes) where ID_Player = %s and ID_Match = %s and ID_Kind = %s and round(gameMinute) = round(%s)"
        params = (item['ID_Player'], item['ID_Match'], item['ID_Kind'], item['gameMinute'])
        tx.execute(query, params)
        result = tx.fetchone()    
        if not result:
            query = """insert into Highlights (ID_Match, ID_Player, ID_Team, ID_Kind, gameMinute, ID_Sub_Player) values (%s, %s, %s, %s, %s, %s)"""
            params = (item['ID_Match'], item['ID_Player'], item['ID_Team'], item['ID_Kind'], item['gameMinute'], item['ID_Sub_Player'])
            tx.execute(query, params)

        
class ProcessSquadItem(object):
    def __init__(self):
        # hardcoded db settings
        self.dbpool = adbapi.ConnectionPool('MySQLdb',
                db=MySQLSettings.db_name,
                user=MySQLSettings.username,
                passwd=MySQLSettings.password,
                cursorclass=MySQLdb.cursors.DictCursor,
                charset='utf8',
                use_unicode=True
        )
    def process_item(self, item, spider):
        # run db query in thread pool
        if isinstance(item, SquadItem) is True:
            query = self.dbpool.runInteraction(self._conditional_insert, item)
        return item
    
    def _conditional_insert(self, tx, item):

        # find ID_Player from Players in db, create one if  not existed
        tx.execute("select ID_Player, ID_Team from Players use index(idx_players) where alias = %s", (item['player']))
        result = tx.fetchone()    
        if result:
            item['ID_Player'] = result['ID_Player']
            item['ID_Team'] = result['ID_Team']
        else:
            # find ID_Home from Teams in db, create one if  not existed
            tx.execute("select ID_Team from Teams use index(idx_teams) where nameTeam = %s", (item['team']))
            result = tx.fetchone()
            while not result:
                # obtain ID_Team
                time.sleep(2)
                tx.execute("select ID_Team from Teams use index(idx_teams) where nameTeam = %s", (item['team']))
                result = tx.fetchone()
            item['ID_Team'] = result['ID_Team']

            query = """insert into Players (alias, ID_Team) values (%s, %s)"""
            params = (item['player'], item['ID_Team'])
            tx.execute(query, params)
            item['ID_Player'] = tx.lastrowid
        if not item['shift_number']:
            item['shift_number'] = 0

        tx.execute("select ID_Match, ID_Player from Squads where ID_Match = %s and ID_Player = %s",(item['ID_Match'], item['ID_Player']))
        result = tx.fetchone()
        if result:
            pass
        else:
            query = """insert into Squads (ID_Match, ID_Player, ID_Team, shiftNumber, `order`) values (%s, %s, %s, %s, %s)"""
            params = (int(item['ID_Match']), item['ID_Player'], item['ID_Team'], int(item['shift_number']), item['order'],)
            tx.execute(query, params)
        